package eBrain.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.UUID;
import java.util.Date;

import eBrain.beans.Bid;
import eBrain.beans.Item;

public class ItemPeer {

	public static ArrayList getItems(DataManager dataManager) {
		ArrayList<Item> items = new ArrayList<Item>();
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "select ITEM_ID, ITEM_NAME, DATE_START, DATE_END, START_BID,DESCRIPTION from ITEM ORDER BY DATE_END desc";

				try {
					ResultSet rs = s.executeQuery(sql);
					try {
						while (rs.next()) {
							Item item = new Item();
							item.setId(rs.getInt(1));
							item.setName(rs.getString(2));
							item.setStart(rs.getTimestamp(3));
							item.setEnd(rs.getTimestamp(4));
							item.setStartBid(rs.getDouble(5));
							item.setDescription(rs.getString(6));
							items.add(item);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get items: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		return items;
	}

	public static ArrayList getItemsByCategory(String Cat,
			DataManager dataManager) {
		ArrayList<Item> items = new ArrayList<Item>();
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "select i.ITEM_ID, ITEM_NAME, DATE_START, DATE_END, START_BID ,DESCRIPTION, c.CATEGORY_ID, c.SUPER_CATEGORY_ID, i.USER_ID from ITEM i, CATEGORY c, CATEGORY_ID_ITEM_ID k where i.ITEM_ID = k.ITEM_ID and c.CATEGORY_ID = k.CATEGORY_ID and c.SUPER_CATEGORY_ID = (select c.CATEGORY_ID from CATEGORY c where c.CATEGORY_ID = "
						+ Cat + ")";
				Statement s2 = connection.createStatement();
				String sql2 = "select i.ITEM_ID, ITEM_NAME, DATE_START, DATE_END, START_BID, DESCRIPTION, c.CATEGORY_ID, c.SUPER_CATEGORY_ID, i.USER_ID from ITEM i, CATEGORY c, CATEGORY_ID_ITEM_ID k where i.ITEM_ID = k.ITEM_ID and c.CATEGORY_ID = k.CATEGORY_ID and	(c.CATEGORY_ID = "
						+ Cat + " AND c.SUPER_CATEGORY_ID IS NULL)";
				try {
					ResultSet rs = s.executeQuery(sql);
					ResultSet rs2 = s2.executeQuery(sql2);
					try {

						while (rs.next()) {
							Item item = new Item();
							item.setId(rs.getInt(1));
							item.setName(rs.getString(2));
							item.setStart(rs.getDate(3));
							item.setEnd(rs.getDate(4));
							item.setStartBid(rs.getDouble(5));
							item.setDescription(rs.getString(6));
							item.setUserid(rs.getInt(9));
							items.add(item);
						}
						while (rs2.next()) {
							Item item = new Item();
							item.setId(rs2.getInt(1));
							item.setName(rs2.getString(2));
							item.setStart(rs2.getDate(3));
							item.setEnd(rs2.getDate(4));
							item.setStartBid(rs2.getDouble(5));
							item.setDescription(rs2.getString(6));
							item.setUserid(rs2.getInt(9));
							items.add(item);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get items ItemPeer.getItemsByCategory: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		return items;
	}
	
	public static ArrayList getItemsByCategory(DataManager dm, int cid) {
		int sid = dm.getSuperCategoryId(cid);
		ArrayList<Item> items = new ArrayList<Item>();
		Connection connection = dm.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT i.ITEM_ID, ITEM_NAME, DATE_START, DATE_END, DESCRIPTION, " +
								"c.CATEGORY_ID, c.SUPER_CATEGORY_ID " +
						"FROM ITEM i, CATEGORY c, CATEGORY_ID_ITEM_ID ci " +
						"WHERE i.ITEM_ID = ci.ITEM_ID " +
						"AND c.CATEGORY_ID = ci.CATEGORY_ID " +
						"AND c.CATEGORY_ID = " + cid + 
						"AND c.SUPER_CATEGORY_ID = " + sid;
						while (dm.getSuperCategoryId(sid) != 0){
							sid = dm.getSuperCategoryId(sid);
						}
						sql = sql + " AND c.SUPER_CATEGORY_ID = " + sid;
						try {
					ResultSet rs = s.executeQuery(sql);
					try {

						while (rs.next()) {
							Item item = new Item();
							item.setId(rs.getInt(1));
							item.setName(rs.getString(2));
							item.setStart(rs.getDate(3));
							item.setEnd(rs.getDate(4));
							item.setDescription(rs.getString(5));
							items.add(item);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get items: " + e.getMessage());
			} finally {
				dm.putConnection(connection);
			}
		}
		return items;
	}

	public static ArrayList getItemsByUserID(DataManager dataManager, int USER_ID){
		ArrayList<Item> items = new ArrayList<Item>();
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "select ITEM_ID, ITEM_NAME, DATE_START, DATE_END, START_BID ,DESCRIPTION, USER_ID from ITEM WHERE USER_ID="+USER_ID;

				try {
					ResultSet rs = s.executeQuery(sql);
					try {
						while (rs.next()) {
							Item item = new Item();
							item.setId(rs.getInt(1));
							item.setName(rs.getString(2));
							item.setStart(rs.getDate(3));
							item.setEnd(rs.getDate(4));
							item.setStartBid(rs.getDouble(5));
							item.setDescription(rs.getString(6));
							item.setUserid(rs.getInt(7));
							items.add(item);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get items ItemPeer.getItemByUserId: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		return items;
	}
	
	public static Item getItem(DataManager dataManager, String ItemID) {
		Item item = new Item();
		Connection connection = dataManager.getConnection();
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT ITEM_ID, ITEM_NAME, DATE_START, DATE_END, START_BID , DESCRIPTION, USER_ID FROM ITEM WHERE ITEM_ID='"
						+ ItemID + "'";

				try {
					ResultSet rs = s.executeQuery(sql);
					try {
						while (rs.next()) {
							item.setId(rs.getInt(1));
							item.setName(rs.getString(2).trim());
							item.setStart(rs.getTimestamp(3));
							item.setEnd(rs.getTimestamp(4));
							item.setStartBid(rs.getDouble(5));
							item.setDescription(rs.getString(6).trim());
							item.setUserid(rs.getInt(7));
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get item (ItemPeer.getItem) : " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}

		return item;
	}

	public static Item setItem(DataManager dataManager, Item item) {
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		if (connection != null) {
			try {		
				
				String sql = "INSERT INTO ITEM VALUES (?,?,?,?,?,?,?)";
				pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1, getItemSeq(dataManager));
				pstmt.setInt(2, item.getUserid());
				pstmt.setString(3, item.getName());
				pstmt.setTimestamp(4, new java.sql.Timestamp(item.getStart().getTime()));
				pstmt.setTimestamp(5, new java.sql.Timestamp(item.getEnd().getTime()));
				pstmt.setDouble(6, item.getStartBid());
				pstmt.setString(7, item.getDescription());
				
				
				try {
					ResultSet rs = pstmt.executeQuery();
					try {
						
					} finally {
						rs.close();
					}
				} finally {
					pstmt.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not store the new item: " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}

		return item;
	}

	

	public static boolean updateItem(DataManager dataManager, Item item){
	   Connection conn = dataManager.getConnection();
	   PreparedStatement pstmt = null;
	
	try {
	      
		  String sql = "UPDATE ITEM SET ITEM_NAME=?, DATE_START=?, DATE_END=?, DESCRIPTION=? WHERE ITEM_ID=?";
	      pstmt = conn.prepareStatement(sql); // create a statement
	      pstmt.setString(1, item.getName());// set input parameter 1
  			pstmt.setTimestamp(2, new java.sql.Timestamp(item.getStart().getTime()));
  			pstmt.setTimestamp(3, new java.sql.Timestamp(item.getEnd().getTime()));
  			pstmt.setString(4, item.getDescription());	
  			pstmt.setInt(5, item.getId()); // set input parameter 2
	      
	      pstmt.executeUpdate(); // execute update statement
	    } catch (Exception e) {
	      e.printStackTrace();
	      System.exit(1);
	    } finally {
	      try {
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	      try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	    }
	    return true;
	  }
	
	public static int getItemSeq(DataManager dataManager){
		
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		int seqcount = 0;
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT eBrain.seq_item.nextval FROM dual";
				pstmt = connection.prepareStatement(sql); 
				
				
				try {
					ResultSet rs = pstmt.executeQuery();
					try {
						while (rs.next()) {
							seqcount=rs.getInt(1);
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
					pstmt.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get UserManager.getUserIDSequence Seq Int fromseq_member"+ e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
			
		return seqcount;
	}
	
	public static boolean hasBid(DataManager dataManager, int itemid){
		
		boolean val = false; 
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT * FROM BID b, ITEM i WHERE b.ITEM_ID=i.ITEM_ID and b.ITEM_ID=?";
				pstmt = connection.prepareStatement(sql); 
				pstmt.setInt(1, itemid);
				ResultSet rs = pstmt.executeQuery();		

				try {
					try {
						while (rs.next()) {
							val = true;
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not test ItemPeer.hasBid  for : "+ itemid+ "  " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}

		return val;
	}
	
}
